﻿using Npgsql;
using System.Data;
using System.Data.Common;
using System.Data.SQLite;

namespace StandardManager.DB
{
    /// <summary>
    /// 支持sqlite和pg
    /// </summary>
    public class ConnectionDatabase_S2
    {
        public string connectionStr;
        public string connectionType;
        private DbConnection conn;

        #region 静态方法
        public static ConnectionDatabase_S2 BuildPGConnection(string ip, string port, string database, string uid, string password)
        {
            if (string.IsNullOrEmpty(port))
            {
                port = "5432";
            }
            var str = string.Format("Server={0};Port={1};Database={2};Uid={3};Pwd={4};",
                 ip, port, database, uid, password);
            return new ConnectionDatabase_S2(str, "PG");
        }
        public static ConnectionDatabase_S2 BuildSqliteConnection(string dbFileName)
        {
            return new ConnectionDatabase_S2(dbFileName, "Sqlite");
        }
        #endregion

        #region 构造函数
        /// <summary>
        /// 
        /// </summary>
        /// <param name="connectionStr"></param>
        /// <param name="dbEngineType">数据库引擎类型</param>
        public ConnectionDatabase_S2(string connectionStr, string dbEngineType)
        {
            this.connectionStr = connectionStr;
            this.connectionType = dbEngineType.ToLower();

            if (dbEngineType.ToLower() == "sqlite")
            {
                if (connectionStr.StartsWith("data source") == false)
                {
                    this.connectionStr = string.Format("data source ={0};", connectionStr);
                }
            }

            GetDbConnection();
        }

        public DbConnection GetDbConnection()
        {
            if (conn is null)
            {
                switch (connectionType.ToLower())
                {
                    case "sqlite":
                        conn = new SQLiteConnection(connectionStr);
                        break;
                    case "pg":
                    case "postgres":
                        conn = new NpgsqlConnection(connectionStr);
                        break;
                }
            }
            return conn;
        }
        #endregion

        public DbCommand GetDbCommand(string sql)
        {
            switch (connectionType)
            {
                case "sqlite":
                    return sql.Length > 0 ? new SQLiteCommand(sql, (SQLiteConnection)conn) : new SQLiteCommand();
                case "pg":
                case "postgres":
                    return sql.Length > 0 ? new NpgsqlCommand(sql, (NpgsqlConnection)conn) : new NpgsqlCommand();
                default:
                    return null;
            }
        }

        public void ExecuteSqlTran(List<string> sqlList)
        {
            try
            {
                conn.Open();
                DbCommand objCommand = GetDbCommand("");
                objCommand.Connection = conn;
                DbTransaction tx = conn.BeginTransaction();
                objCommand.Transaction = tx;
                for (int i = 0; i < sqlList.Count; ++i)
                {
                    objCommand.CommandText = sqlList[i];
                    objCommand.ExecuteNonQuery();
                }
                tx.Commit();
                objCommand.Dispose();
            }
            finally
            {
                conn.Close();
            }
        }

        #region 执行语句
        public void ExecuteSQL(string sql)
        {
            if (m_isInTransaction)
            {
                DbCommand objCommand = GetDbCommand(sql);
                objCommand.ExecuteNonQuery();
            }
            else
            {
                try
                {
                    conn.Open();
                    DbCommand objCommand = GetDbCommand(sql);
                    objCommand.ExecuteNonQuery();
                    objCommand.Dispose();
                }
                finally
                {
                    conn.Close();
                }
            }
        }
        #endregion

        #region 插入
        public void InsertData(string insertSql, string imgFieldName, byte[] imageData)
        {
            conn.Open();
            try
            {
                // 插入图片数据  
                string insertQuery = insertSql;
                using (DbCommand command = GetDbCommand(insertQuery))
                {
                    if (string.IsNullOrEmpty(imgFieldName) == false)
                    {
                        ((SQLiteCommand)command).Parameters.AddWithValue("@" + imgFieldName, imageData);
                    }
                    command.ExecuteNonQuery();
                }
            }
            catch (Exception)
            {

                throw;
            }
            finally
            {
                conn.Close(); 
            }

        }
        #endregion

        /// <summary>
        /// 通过某个条件查
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public DataTable SelectData(string sql, string[] bytesIndex=null)
        {
            try
            {
                conn.Open();

                DataTable dt = new DataTable();
                DbCommand objCommand = GetDbCommand(sql);
                DbDataReader dr = objCommand.ExecuteReader();
                for (var i = 0; i < dr.FieldCount; i++)
                {
                    var fieldname=dr.GetName(i);
                    if (bytesIndex != null && bytesIndex.Contains(fieldname))
                    {
                        dt.Columns.Add(dr.GetName(i), typeof(byte[]));
                    }
                    else
                    {
                        dt.Columns.Add(dr.GetName(i));
                    }
                }
                while (dr.Read())
                {
                    DataRow dtr = dt.NewRow();
                    for (var i = 0; i < dr.FieldCount; i++)
                    {
                        dtr[i] = dr.GetValue(i);
                    }
                    dt.Rows.Add(dtr);
                }

                dr.Close();
                objCommand.Dispose();

                return dt;
            }
            finally
            {
                conn.Close();
            }
        }

        /// <summary>
        /// 获取数据表的主键
        /// </summary>
        /// <param name="tablename"></param>
        /// <returns></returns>
        public string GetPrimaryKey(string tablename)
        {
            try
            {
                conn.Open();

                string primaryKey = "";
                string sql;
                DbCommand objCommand;
                DbDataReader dr;
                switch (connectionType)
                {
                    case "Sqlite":
                        sql = $"PRAGMA table_info(\'{tablename}\');";
                        objCommand = GetDbCommand(sql);
                        dr = objCommand.ExecuteReader();
                        int pkIndex = -1;
                        for (var i = 0; i < dr.FieldCount; i++)
                        {
                            if (dr.GetName(i) == "pk")
                            {
                                pkIndex = i;
                            }
                        }
                        if (pkIndex == -1)
                        {
                            return "";
                        }
                        while (dr.Read())
                        {
                            var v=dr.GetValue(pkIndex).ToString();
                            var tv = int.Parse(v);
                            if (tv == 1)
                            {
                                return dr.GetString("name") as string ?? "";
                            }
                        }
                        break;
                    case "PG":
                        sql = $"SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE TABLE_NAME=\'{tablename}\'";
                        objCommand = GetDbCommand(sql);
                        dr = objCommand.ExecuteReader();
                        while (dr.Read())
                        {
                            primaryKey = dr.GetValue(0).ToString() ?? "";
                        }
                        break;
                    default:
                        return "";
                }

                return primaryKey;
            }
            finally
            {
                conn.Close();
            }
        }

        public string GetDatabaseName()
        {
            switch (connectionType.ToLower())
            {
                case "sqlite":
                    return Path.GetFileName(GetIP());
                case "pg":
                    return conn.Database;
                default:
                    return "";
            }
        }

        public string GetIP()
        {
            switch (connectionType.ToLower())
            {
                case "sqlite":
                    int nextIndex = connectionStr.IndexOf(':');
                    string rightStr = connectionStr.Substring(0,connectionStr.Count() - nextIndex + 1);
                    int finalIndex = rightStr.IndexOf(';');
                    if (finalIndex == -1)
                    {
                        return rightStr;
                    }
                    return rightStr.Substring(finalIndex);
                case "pg":
                    return "";
                default:
                    return "";
            }
        }

        #region 事务
        private bool m_isInTransaction = false;
        private DbTransaction m_dbTransaction;
        public void BeginTransaction()
        {
            conn.Open();
            m_dbTransaction = conn.BeginTransaction();
            m_isInTransaction = true;
        }
        public void CommitTransaction()
        {
            m_dbTransaction.Commit();
            m_isInTransaction = false;
            conn.Close();
        }
        #endregion

        #region 获得所有的表
        public List<string> GetTableNames()
        {
            var sql = "SELECT name FROM sqlite_master WHERE type = 'table'";
            if (connectionType == "sqlite")
            {
                sql = "SELECT name FROM sqlite_master WHERE type = 'table'";

            }
            else if (connectionType == "")
            {
                sql = "SELECT table_name FROM information_schema.tables WHERE table_schema = 'public'";
            }
            var dt = SelectData(sql);
            var list = new List<string>();
            for (var i = 0; i < dt.Rows.Count; i++)
            {
                list.Add(dt.Rows[i][0].ToString());
            }
            return list;
        }
        #endregion

        public List<string> GetRowNames(string tableName)
        {
            List<string> rowNames = new List<string>();
            var dt = SelectData("SELECT * From " + tableName + ";");
            foreach (DataColumn column in dt.Columns)
            {
                rowNames.Add(column.ColumnName);
            }
            return rowNames.Count > 0 ? rowNames : null;
        }

        public void Dispose()
        {
            conn.Close();
            conn.Dispose();
        }
    }
}
